import pymysql

def connect_mysql():
    """连接MySQL数据库并设置字符集"""
    try:
        conn = pymysql.connect(
            host='localhost',
            user='root',
            password='Ww147258@',
            database='ccd',
            charset='utf8mb4',
            cursorclass=pymysql.cursors.DictCursor
        )
        # 双重确保连接使用utf8mb4字符集
        with conn.cursor() as cur:
            cur.execute('SET NAMES utf8mb4;')
            cur.execute('SET CHARACTER SET utf8mb4;')
            cur.execute('SET character_set_connection=utf8mb4;')
        print("数据库连接成功")
        return conn
    except pymysql.Error as e:
        print(f"数据库连接失败: {e}")
        return None

def create_table(conn):
    """创建students表（明确指定字符集）"""
    try:
        with conn.cursor() as cur:
            # 删除旧表（如果存在）
            cur.execute("DROP TABLE IF EXISTS students")
            
            # 创建新表，指定字符集和排序规则
            sql = """
            CREATE TABLE students (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(50) NOT NULL,
                age INT,
                gender ENUM('M', 'F'),
                major VARCHAR(50)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            """
            cur.execute(sql)
        print("数据表创建成功")
    except pymysql.Error as e:
        print(f"创建表失败: {e}")

def insert_data(conn, name, age, gender, major):
    """插入单条学生数据"""
    try:
        with conn.cursor() as cur:
            sql = "INSERT INTO students (name, age, gender, major) VALUES (%s, %s, %s, %s)"
            cur.execute(sql, (name, age, gender, major))
        conn.commit()  # 提交事务
        print(f"插入数据成功: {name}")
    except pymysql.Error as e:
        conn.rollback()  # 回滚事务
        print(f"插入数据失败: {e}")

def query_data(conn, min_age=0):
    """查询年龄大于等于min_age的学生"""
    try:
        with conn.cursor() as cur:
            sql = "SELECT * FROM students WHERE age >= %s"
            cur.execute(sql, (min_age,))
            results = cur.fetchall()
            print(f"查询到 {len(results)} 条记录")
            for row in results:
                print(f"ID: {row['id']}, 姓名: {row['name']}, 年龄: {row['age']}, "
                      f"性别: {row['gender']}, 专业: {row['major']}")
    except pymysql.Error as e:
        print(f"查询数据失败: {e}")

def update_data(conn, old_name, new_age):
    """更新指定姓名学生的年龄"""
    try:
        with conn.cursor() as cur:
            sql = "UPDATE students SET age = %s WHERE name = %s"
            cur.execute(sql, (new_age, old_name))
        conn.commit()
        print(f"更新数据成功: {old_name}")
    except pymysql.Error as e:
        conn.rollback()
        print(f"更新数据失败: {e}")

def delete_data(conn, name):
    """删除指定姓名的学生"""
    try:
        with conn.cursor() as cur:
            sql = "DELETE FROM students WHERE name = %s"
            cur.execute(sql, (name,))
        conn.commit()
        print(f"删除数据成功: {name}")
    except pymysql.Error as e:
        conn.rollback()
        print(f"删除数据失败: {e}")

if __name__ == "__main__":
    conn = connect_mysql()
    if conn:
        try:
            # 1. 创建表（会先删除旧表）
            create_table(conn)
            
            # 2. 插入示例数据
            insert_data(conn, "张三", 20, "M", "计算机科学")
            insert_data(conn, "李四", 22, "F", "电子工程")
            insert_data(conn, "王五", 19, "M", "机械设计")
            
            # 3. 查询数据
            print("\n=== 查询年龄大于等于20的学生 ===")
            query_data(conn, 20)
            
            # 4. 更新数据
            print("\n=== 更新张三的年龄为23 ===")
            update_data(conn, "张三", 23)
            
            # 5. 再次查询
            print("\n=== 更新后的查询结果 ===")
            query_data(conn, 20)
            
            # 6. 删除数据
            print("\n=== 删除王五的记录 ===")
            delete_data(conn, "王五")
            
            # 7. 最终查询
            print("\n=== 删除后的查询结果 ===")
            query_data(conn, 0)
            
        finally:
            conn.close()
            print("数据库连接已关闭")